package cn.dida.dao;

import cn.dida.model.*;
import org.apache.commons.collections.map.HashedMap;
import org.apache.commons.lang3.ArrayUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.*;

/**
 * Created by zhangzhiling on 2017/8/30.
 */
@Repository
public class SalesmanPayruleDao  extends DaoBase  {

    /**
     * 获取列表页
     * @param companyId
     * @param name
     * @param adcode
     * @param state
     * @param page
     * @return
     */
    public Pageable<SalesmanPayrule> queryList(Integer companyId, String name, String adcode, Integer state, Integer page) {
        Object[] args = {};
        StringBuffer sqlQueryResultCount = new StringBuffer("select count(1) from salesman_payrule_base b ");
        StringBuffer sqlQueryResult = new StringBuffer("select b.id,b.name,b.valid_begin_time,b.valid_end_time," +
                "b.payway,b.day_limit,b.pay_limit,b.pay_condition,b.c_time,b.state,s.account " +
                " from salesman_payrule_base b left join" +
                "  staff s on s.id=b.staff_id");

        StringBuffer sqlWhere = new StringBuffer(" where b.company_id=?");
        args = ArrayUtils.add(args, companyId);
        if (state != null) {
            sqlWhere.append(" and b.state = ?");
            args = ArrayUtils.add(args, state);
        }
        if (name != null && name !="") {
            sqlWhere.append(" and b.name like ?");
            args = ArrayUtils.add(args, "%" + name + "%");
        }
//        if (adcode != null && adcode !="") {
//            sqlWhere.append(" and a.adcode = ?");
//            args = ArrayUtils.add(args, adcode);
//        }

        // 查询记录总数
        Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(sqlWhere).toString(), Integer.class, args);
        // 查询记录
        sqlQueryResult.append(sqlWhere).append("  order by c_time desc limit ?,?");
        args = ArrayUtils.add(args, (page - 1) * getPageSize());
        args = ArrayUtils.add(args, getPageSize());
        List<SalesmanPayrule> result = jdbcTemplate.query(sqlQueryResult.toString(), args, new BeanPropertyRowMapper<>(SalesmanPayrule.class));
        return new Pageable<>(page, getPageSize(), resultTotal, result);
    }

    /**
     * 导出 列表
     * @param companyId
     * @param name
     * @param state
     * @return
     */
    public List<SalesmanPayrule> exportList(Integer companyId, String name, Integer state) {
        Object[] args = {};
        StringBuffer sqlQueryResult = new StringBuffer("select b.id,b.name,b.valid_begin_time,b.valid_end_time," +
                "b.payway,b.day_limit,b.pay_limit,b.pay_condition,b.c_time,b.state,s.account " +
                " from salesman_payrule_base b left join" +
                "  staff s on s.id=b.staff_id");

        StringBuffer sqlWhere = new StringBuffer(" where b.company_id=?");
        args = ArrayUtils.add(args, companyId);
        if (state != null) {
            sqlWhere.append(" and b.state = ?");
            args = ArrayUtils.add(args, state);
        }
        if (name != null && name !="") {
            sqlWhere.append(" and b.name like ?");
            args = ArrayUtils.add(args, "%" + name + "%");
        }

        // 查询记录
        sqlQueryResult.append(sqlWhere).append("  order by c_time desc");

        List<SalesmanPayrule> result = jdbcTemplate.query(sqlQueryResult.toString(), args, new BeanPropertyRowMapper<>(SalesmanPayrule.class));
        return result;
    }

    /**
     * 添加支付规则
     * @param payrule
     * @return
     */
    @Transactional
    public Integer insertPayrule (SalesmanPayrule payrule) {

        String sql = "insert into salesman_payrule_base(company_id, name,valid_begin_time,valid_end_time,payway, " +
                "day_limit, pay_condition, pay_limit,c_time, state,staff_id) " +
                "values (:company_id, :name, :valid_begin_time, :valid_end_time, :payway," +
                " :day_limit, :pay_condition, :pay_limit, :c_time, :state,:staff_id)";

        Long currentDate = new Date().getTime();
        payrule.setC_time(currentDate);
        payrule.setState(0);

        //创建单据
        KeyHolder keyHolder=new GeneratedKeyHolder();
        SqlParameterSource ps=new BeanPropertySqlParameterSource(payrule);
        namedParameterJdbcTemplate.update(sql, ps, keyHolder);
        //新创建的id
        int payruleId = keyHolder.getKey().intValue();

        //创建区域关联
        SalesmanPayruleArea area[] = payrule.getArea();
        if(area.length>0){
            for(int i = 0; i < area.length; i++){
                area[i].setPayrule_base_id(payruleId);
                area[i].setC_time(currentDate);
                insert_salesman_payrule_area(area[i]);

            }
        }


        //创建产品区域关联 获取区域详细列表
        List<SalesmanPayruleArea> areaList = new ArrayList<>();
        if(area.length>0){
            for(int i = 0; i < area.length; i++){
               String adcode = area[i].getAdcode();
               String adname = area[i].getAdname();
                Integer adlevel = getAdcodeLeve(adcode);
                if(adlevel == null){
                    areaList.add(area[i]);
                }else if(adlevel == 0){
                    List<SalesmanPayruleArea> cityList = getAdcodeByLevel(adcode,adname,1);
                    for(SalesmanPayruleArea code:cityList){
                        List<SalesmanPayruleArea> areas = getAdcodeByLevel(code.getAdcode() ,code.getAdname(),2);
                        areaList.addAll(areas);
                    }
                }else if(adlevel==1){
                    List<SalesmanPayruleArea> areas = getAdcodeByLevel(adcode,adname,2);
                    areaList.addAll(areas);
                }else{

                    areaList.add(area[i]);
                }
            }
        }

        //创建产品关联
        SalesmanPayrulePro pros[] = payrule.getPros();
        if(pros.length>0){
            for(int i = 0; i < pros.length; i++){
                pros[i].setPayrule_base_id(payruleId);
                pros[i].setC_time(currentDate);
                insert_salesman_payrule_prod(pros[i]);

                for(SalesmanPayruleArea ar:areaList){
                    Map<String,Object> map = new HashedMap();
                    map.put("payrule_base_id",payruleId);
                    map.put("proId",pros[i].getProId());
                    map.put("price",pros[i].getPrice());
                    map.put("adcode",ar.getAdcode());
                    map.put("adname",ar.getAdname());
                    map.put("c_time",currentDate);

                    insert_salesman_payrule_prodandarea(map);

                }
            }
        }
        return 1;
    }

    /**
     *  添加规则产品区域关联
     * @param map
     * @return
     */
    public Integer insert_salesman_payrule_prodandarea(Map<String,Object> map){

        String sql = "insert into salesman_payrule_prodandarea(payrule_base_id, product_id,adcode,adname," +
                "bottle_price,c_time,state) " +
                "values (:payrule_base_id, :proId, :adcode, :adname,:price,:c_time,:state)";
        HashMap<String, Object> paramMap = new HashMap<>();
        paramMap.put("payrule_base_id", map.get("payrule_base_id"));
        paramMap.put("proId", map.get("proId"));
        paramMap.put("adcode", map.get("adcode"));
        paramMap.put("adname", map.get("adname"));
        paramMap.put("price", map.get("price"));
        paramMap.put("c_time", map.get("c_time"));
        paramMap.put("state", 0);
        return namedParameterJdbcTemplate.update(sql, paramMap);
    }

    /**
     * 获取区域等级
     * @param adcode
     * @return
     */
    public Integer getAdcodeLeve(String adcode){

        String sql ="select adlevel from addoc where adcode =" + adcode;
        List<Map<String,Object>> list = jdbcTemplate.queryForList(sql);
        if (null != list && list.size() == 1) {
            return (Integer) list.get(0).get("adlevel");
        }
        return null;
    }

    /**
     * 获取 下级区域
     * @param adcode 上级区域编码
     * @param adlevel 要获取的区域的等级
     * @return
     */
    public List<SalesmanPayruleArea> getAdcodeByLevel(String adcode, String name,Integer adlevel){

        Object[] args = {};
        Integer i = 2;
        if(adlevel == 2){
            i=4;
        }
        adcode= adcode.substring(0,i);
        String sql ="select adcode,concat('"+name+"','-',adname) 'adname' from addoc where adlevel = ? and adcode like ?" ;
        args = ArrayUtils.add(args,adlevel);
        args = ArrayUtils.add(args,  adcode + "%");
        List<SalesmanPayruleArea> list = jdbcTemplate.query(sql,args, new BeanPropertyRowMapper<SalesmanPayruleArea>(SalesmanPayruleArea.class));
        return list;
    }

    /**
     * 删除规则产品区域关联
     * @param payrule_base_id
     * @return
     */
    private Integer delete_payrule_prodandarea(Integer payrule_base_id){
        String sql = "delete from salesman_payrule_prodandarea where payrule_base_id = ?";
        return jdbcTemplate.update(sql,payrule_base_id);
    }
    private Integer update_payrule_prodandarea(Integer proId,String adcode){
        String sql = "update salesman_payrule_prodandarea set state =2 where product_id = ? and adcode =?";
        return jdbcTemplate.update(sql,proId,adcode);
    }

    /**
     * 添加支付规则关联产品
     * @param payrulePro
     * @return
     */
    public Integer insert_salesman_payrule_prod(SalesmanPayrulePro payrulePro){

        String sql = "insert into salesman_payrule_prod(payrule_base_id, product_id,bottle_price,c_time) " +
                "values (:payrule_base_id, :proId, :price, :c_time)";
        HashMap<String, Object> paramMap = new HashMap<>();
        paramMap.put("payrule_base_id", payrulePro.getPayrule_base_id());
        paramMap.put("proId", payrulePro.getProId());
        paramMap.put("price", payrulePro.getPrice());
        paramMap.put("c_time", payrulePro.getC_time());
        return namedParameterJdbcTemplate.update(sql, paramMap);
    }

    /**
     * 删除支付规则关联产品
     * @param payrule_base_id
     * @return
     */
    private Integer delete_payrule_prod(Integer payrule_base_id){
        String sql = "delete from salesman_payrule_prod where payrule_base_id = ?";

        return jdbcTemplate.update(sql,payrule_base_id);
    }

    /**
     * 添加支付规则 关联区域
     * @return
     */
    public Integer insert_salesman_payrule_area(SalesmanPayruleArea payruleArea){

        String sql = "insert into salesman_payrule_area(payrule_base_id, adcode,adname,c_time,advalue) " +
                "values (:payrule_base_id, :adcode, :adname, :c_time,:advalue)";
        HashMap<String, Object> paramMap = new HashMap<>();
        paramMap.put("payrule_base_id",payruleArea.getPayrule_base_id());
        paramMap.put("adcode", payruleArea.getAdcode());
        paramMap.put("adname",payruleArea.getAdname());
        paramMap.put("c_time", payruleArea.getC_time());
        paramMap.put("advalue", payruleArea.getAdvalue());
        return namedParameterJdbcTemplate.update(sql, paramMap);
    }

    /**
     * 删除支付规则关联区域
     * @param payrule_base_id
     * @return
     */
    private Integer delete_payrule_area(Integer payrule_base_id){
        String sql = "delete from salesman_payrule_area where payrule_base_id = ?";

        return jdbcTemplate.update(sql,payrule_base_id);
    }

    /**
     *  获取支付规则base 详情
     * @param baseId
     * @return
     */
    public SalesmanPayrule get_payrule_base(Integer baseId){

        String sql = "select id,name,valid_begin_time,valid_end_time,payway,day_limit,pay_condition,pay_limit," +
                "state from salesman_payrule_base where id=" + baseId;

        List<SalesmanPayrule> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<SalesmanPayrule>(SalesmanPayrule.class));
        if (null != list && list.size() == 1) {
            return list.get(0);
        }
        return null;

    }

    /**
     * 获取支付规则关联产品
     * @param baseId
     * @return
     */
    public List<SalesmanPayrulePro> get_payrule_prod(Integer baseId){
        String sql = "select s.payrule_base_id,s.bottle_price 'price',p.id 'proId',p.name 'proName', p.number 'proNumber'" +
                " from salesman_payrule_prod s join product p on p.id=s.product_id" +
                " where s.payrule_base_id=" + baseId;

        List<SalesmanPayrulePro> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<SalesmanPayrulePro>(SalesmanPayrulePro.class));
        return list;
    }

    /**
     * 获取支付规则关联区域
     * @param baseId
     * @return
     */
    public List<SalesmanPayruleArea> get_payrule_area(Integer baseId){
        String sql = "select a.payrule_base_id,a.adcode,a.adname,a.advalue,ad.adlevel " +
                "from salesman_payrule_area a left join addoc ad on ad.adcode=a.adcode  where a.payrule_base_id=" + baseId;

        List<SalesmanPayruleArea> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<SalesmanPayruleArea>(SalesmanPayruleArea.class));
        return list;
    }

    /**
     * 根据baseId获取产品区域列表数据
     * @param baseId
     * @return
     */
    public List<Map<String,Object>> getProdAndAreaByBaseId (Integer baseId){
        String sql = "select s.adcode,s.adname,s.state,s.product_id" +
                " from salesman_payrule_prodandarea s  where s.payrule_base_id =" + baseId;
        List<Map<String,Object>> list = jdbcTemplate.queryForList(sql);
        return list;
    }

    /**
     * 启用验证
     * @param baseId
     * @return
     */
    public Boolean test(Integer baseId){
        Boolean  result = true;
        List<Map<String,Object>> list = getProdAndAreaByBaseId(baseId);
        String sql = "select s.payrule_base_id,s.adcode,s.adname,s.state,s.product_id" +
                " from salesman_payrule_prodandarea s  where s.state=1 and s.payrule_base_id !=" + baseId;
        String sqlWhere="";
        if(list != null && list.size()>0){
            for(Map<String,Object> map:list){
                String adcode = (String)map.get("adcode");
                Integer proId = (Integer)map.get("product_id");
                sqlWhere =" and s.adcode='"+adcode +"' and s.product_id="+proId;
                List<Map<String,Object>> li = jdbcTemplate.queryForList(sql+sqlWhere);
                if (null != li && li.size() > 0) {
                    result = false;
                    break;
                }
            }
        }

        return result;
    }

    /**
     * 启动规则是 覆盖原来的正在使用的
     * @param baseId
     * @return
     */
    public  Integer coverProAndArea(Integer baseId){
        Integer count = 0;
        List<Map<String,Object>> list = getProdAndAreaByBaseId(baseId);
        String sql = "update  salesman_payrule_prodandarea set state=2  where state=1 and adcode=:adcode and product_id=:proId and payrule_base_id!=:baseId ";
        if(list != null && list.size()>0){
            for(Map<String,Object> map:list){
                String adcode = (String)map.get("adcode");
                Integer proId = (Integer)map.get("product_id");
                HashMap<String, Object> paramMap = new HashMap<>();
                paramMap.put("adcode", adcode);
                paramMap.put("proId", proId);
                paramMap.put("baseId", baseId);
                count += namedParameterJdbcTemplate.update(sql, paramMap);

            }
        }
        return count;
    }

    /**
     * 更改支付规则的状态 停用/启用
     * @param baseId
     * @param state   1使用中,  3已停用
     * @return
     */
    @Transactional
    public Integer updateState(Integer baseId,Integer state){
        String sql = "update salesman_payrule_base set state=:state where id =" + baseId;
        HashMap<String, Object> paramMap = new HashMap<>();
        paramMap.put("state", state);
        Integer result= namedParameterJdbcTemplate.update(sql, paramMap);
        updateProdAndArea(baseId,state);
        if(state == 1){
            if(!test(baseId)){
                coverProAndArea(baseId);
            }
        }

        return result;
    }

    /**
     * 跟新  _prodandarea  的状态
     * @param baseId
     * @param state
     * @return
     */
    public Integer updateProdAndArea(Integer baseId,Integer state){
        String sql = "update salesman_payrule_prodandarea set state=:state where state=:oldSta  and payrule_base_id =" + baseId;

        Integer oldSta=0;
        if(state == 3){
            state = 2;
            oldSta=1;
        }
        HashMap<String, Object> paramMap = new HashMap<>();
        paramMap.put("state", state);
        paramMap.put("oldSta", oldSta);
        return namedParameterJdbcTemplate.update(sql, paramMap);
    }

    /**
     * 编辑
     * @param payrule
     * @return
     */
    @Transactional
    public Integer updatePayrule(SalesmanPayrule payrule){

        String sql = "update salesman_payrule_base set name=:name,valid_begin_time=:valid_begin_time,valid_end_time=:valid_end_time," +
                "payway=:payway, day_limit=:day_limit,pay_condition=:pay_condition,pay_limit=:pay_limit"+
                " where id="+payrule.getId() ;

        HashMap<String, Object> paramMap = new HashMap<>();
        paramMap.put("name", payrule.getName());
        paramMap.put("valid_begin_time", payrule.getValid_begin_time());
        paramMap.put("valid_end_time", payrule.getValid_end_time());
        paramMap.put("payway", payrule.getPayway());
        paramMap.put("day_limit", payrule.getDay_limit());
        paramMap.put("pay_condition", payrule.getPay_condition());
        paramMap.put("pay_limit", payrule.getPay_limit());
        namedParameterJdbcTemplate.update(sql,paramMap);
        //新创建的id
        int payruleId = payrule.getId();

        Long currentDate = new Date().getTime();
        //创建区域关联
        delete_payrule_area(payruleId);//删除原来关联
        SalesmanPayruleArea area[] = payrule.getArea();
        if(area.length>0){
            for(int i = 0; i < area.length; i++){
                area[i].setPayrule_base_id(payruleId);
                area[i].setC_time(currentDate);
                insert_salesman_payrule_area(area[i]);

            }
        }

        //创建产品区域关联 获取区域详细列表
        List<SalesmanPayruleArea> areaList = new ArrayList<>();
        if(area.length>0){
            for(int i = 0; i < area.length; i++){
                String adcode = area[i].getAdcode();
                String adname = area[i].getAdname();
                Integer adlevel = getAdcodeLeve(adcode);
                if(adlevel == null){
                    areaList.add(area[i]);
                }else  if(adlevel == 0){
                    List<SalesmanPayruleArea> cityList = getAdcodeByLevel(adcode,adname,1);
                    for(SalesmanPayruleArea code:cityList){
                        List<SalesmanPayruleArea> areas = getAdcodeByLevel(code.getAdcode() ,code.getAdname(),2);
                        areaList.addAll(areas);
                    }
                }else if(adlevel==1){
                    List<SalesmanPayruleArea> areas = getAdcodeByLevel(adcode,adname,2);
                    areaList.addAll(areas);
                }else{

                    areaList.add(area[i]);
                }
            }
        }

        //创建产品关联
        delete_payrule_prod(payruleId);//删除原来关联
        delete_payrule_prodandarea(payruleId);
        //创建产品关联
        SalesmanPayrulePro pros[] = payrule.getPros();
        if(pros.length>0){
            for(int i = 0; i < pros.length; i++){
                pros[i].setPayrule_base_id(payruleId);
                pros[i].setC_time(currentDate);
                insert_salesman_payrule_prod(pros[i]);

                for(SalesmanPayruleArea ar:areaList){
                    Map<String,Object> map = new HashedMap();
                    map.put("payrule_base_id",payruleId);
                    map.put("proId",pros[i].getProId());
                    map.put("price",pros[i].getPrice());
                    map.put("adcode",ar.getAdcode());
                    map.put("adname",ar.getAdname());
                    map.put("c_time",currentDate);

                    insert_salesman_payrule_prodandarea(map);

                }
            }
        }
        return 1;
    }

    /**
     * 适用区域产品详情
     * @param adcode
     * @param baseId
     * @return
     */
    public Pageable<Map<String,Object>> getProdAndAreaList(String adcode,String prodct,String area, String baseId,Integer state,Integer page){

        Object[] args = {};
        adcode= adcode.substring(0,3);
        StringBuffer sqlQueryResultCount = new StringBuffer("select count(1) from salesman_payrule_prodandarea s join" +
                " product p on p.id=s.product_id");
        StringBuffer sqlQueryResult = new StringBuffer("select s.adcode,s.adname,s.state,p.name 'proName'" +
                " from salesman_payrule_prodandarea s join product p on p.id=s.product_id " );

        StringBuffer sqlWhere = new StringBuffer(" where payrule_base_id = ? and adcode like ? ");
        args = ArrayUtils.add(args,baseId);
        args = ArrayUtils.add(args,  adcode + "%");
        if (prodct != null && prodct != "") {
            sqlWhere.append(" and (p.name like ? or p.number like ?)");
            args = ArrayUtils.add(args, "%" + prodct + "%");
            args = ArrayUtils.add(args, "%" + prodct + "%");
        }
        if (area != null && area != "") {
            sqlWhere.append(" and s.adname like ?");
            args = ArrayUtils.add(args, "%" + area + "%");
        }
        if (state != null && state != -1) {
            sqlWhere.append(" and s.state = ?");
            args = ArrayUtils.add(args,state);
        }


        // 查询记录总数
        Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(sqlWhere).toString(), Integer.class, args);
        // 查询记录
        sqlQueryResult.append(sqlWhere).append("   limit ?,?");
        args = ArrayUtils.add(args, (page - 1) * getPageSize());
        args = ArrayUtils.add(args, getPageSize());
        List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlQueryResult.toString(), args);
        return new Pageable<>(page, getPageSize(), resultTotal, result);
    }

    /**
     * 规则查询列表
     * @param prodct
     * @param area
     * @param state
     * @param page
     * @return
     */
    public Pageable<Map<String,Object>> payruleSearchList(Integer companyId,String prodct,String area,Integer state,Integer page){

        Object[] args = {};
        StringBuffer sqlQueryResultCount = new StringBuffer("select count(1) from salesman_payrule_prodandarea s join" +
                " product p on p.id=s.product_id join salesman_payrule_base b on b.id=s.payrule_base_id");
        StringBuffer sqlQueryResult = new StringBuffer("select s.adcode,s.adname,s.state,p.name 'proName'," +
                "b.name,b.valid_begin_time,b.valid_end_time,b.day_limit,b.pay_condition,b.pay_limit" +
                " from salesman_payrule_prodandarea s join product p on p.id=s.product_id " +
                "join salesman_payrule_base b on b.id=s.payrule_base_id" );

        StringBuffer sqlWhere = new StringBuffer(" where b.company_id=? ");
        args = ArrayUtils.add(args, companyId);
        if (prodct != null && prodct != "") {
            sqlWhere.append(" and (p.name like ? or p.number like ?)");
            args = ArrayUtils.add(args, "%" + prodct + "%");
            args = ArrayUtils.add(args, "%" + prodct + "%");
        }
        if (area != null && area != "") {
            sqlWhere.append(" and s.adname like ?");
            args = ArrayUtils.add(args, "%" + area + "%");
        }
        if (state != null && state != -1) {
            sqlWhere.append(" and s.state = ?");
            args = ArrayUtils.add(args,state);
        }


        // 查询记录总数
        Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(sqlWhere).toString(), Integer.class, args);
        // 查询记录
        sqlQueryResult.append(sqlWhere).append(" order by s.c_time desc  limit ?,?");
        args = ArrayUtils.add(args, (page - 1) * getPageSize());
        args = ArrayUtils.add(args, getPageSize());
        List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlQueryResult.toString(), args);
        return new Pageable<>(page, getPageSize(), resultTotal, result);
    }

    /**
     * 规则查询列表 导出
     * @param companyId
     * @param prodct
     * @param area
     * @param state
     * @return
     */
    public List<Map<String,Object>> searchList_export(Integer companyId,String prodct,String area,Integer state){

        Object[] args = {};
        StringBuffer sqlQueryResult = new StringBuffer("select s.adcode,s.adname,s.state,p.name 'proName'," +
                "b.name,b.valid_begin_time,b.valid_end_time,b.day_limit,b.pay_condition,b.pay_limit" +
                " from salesman_payrule_prodandarea s join product p on p.id=s.product_id " +
                "join salesman_payrule_base b on b.id=s.payrule_base_id" );

        StringBuffer sqlWhere = new StringBuffer(" where b.company_id=? ");
        args = ArrayUtils.add(args, companyId);
        if (prodct != null && prodct != "") {
            sqlWhere.append(" and (p.name like ? or p.number like ?)");
            args = ArrayUtils.add(args, "%" + prodct + "%");
            args = ArrayUtils.add(args, "%" + prodct + "%");
        }
        if (area != null && area != "") {
            sqlWhere.append(" and s.adname like ?");
            args = ArrayUtils.add(args, "%" + area + "%");
        }
        if (state != null && state != -1) {
            sqlWhere.append(" and s.state = ?");
            args = ArrayUtils.add(args,state);
        }

        // 查询记录
        sqlQueryResult.append(sqlWhere).append(" order by s.c_time desc");

        List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlQueryResult.toString(), args);
        return result;
    }

    /**
     *
     *定时任务，跟新规则主表状态
     * @return
     */
    @Transactional
    public Integer updateStateByQuartz(){

        long nowTime = new Date().getTime();
        String sql = "update salesman_payrule_base set state=2 where state=1 and valid_end_time <=" + nowTime;
        updateByQuartz(nowTime);
        Integer count =  jdbcTemplate.update(sql);
       return count;
    }

    /**
     * 定时任务, 跟新规则字表状态
     * @param nowTime
     * @return
     */
    public Integer updateByQuartz(Long nowTime){
        String sql = "update salesman_payrule_prodandarea set state=2 where state=1 and payrule_base_id in" +
                " (select id from salesman_payrule_base where state=1 and valid_end_time <="+nowTime+")" ;
        Integer count = jdbcTemplate.update(sql);
        return count;
    }
}
